package imageviewerdb;

/*import java.sql.Connection;
import java.sql.DriverManager;*/
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;

import java.util.ArrayList;
public class ImageViewerModel implements Subject, ImageViewerModelInterface{
    
    
    private DBInstance dbi;
    private ArrayList<Observer> observers;
    public ImageViewerModel(){}
    
    /* Fills up the pictures table from an arraylist. */
    public void fillPicturesTable(ArrayList<ImageDb> images){
        
        Statement s = null;
        try {
             s = DBInstance.getDBInstance().conn.createStatement();                
        } catch (SQLException e){e.printStackTrace();}
        
        Iterator I = images.iterator();
        String path, description, size, extension, topic, name;
        boolean confidentiality;
        ImageDb imd;
        
        while (I.hasNext()){
            
              imd = (ImageDb)I.next();
              path = imd.getPath();size = imd.getSize();
              description = imd.getDescription();
              size = imd.getSize();
              extension = imd.getExtension();
              topic = imd.getTopic();
              name = imd.getName();
              confidentiality = imd.getConfidentiality();
              char conf = 'F';  if (confidentiality) conf = 'T';                                       
              
            try{
                 s.execute("insert into pictures values( '" + path + "','" + name + 
                                                        "','" + extension + "','" + description + 
                                                        "','" + size + "','" + topic + "','" + 
                                                        conf + "')");
              }
            catch(SQLException e) {System.out.println("Error updating db."); }
        }
        System.out.println("Base de datos llena");
        // try to close the statement
        try {
        s.close(); } catch(SQLException e) {e.printStackTrace();}
        
    }    
    
    /* Returns an arraylist with all image tuples in favorites table. */
    public ArrayList<ImageDb> getFavorites()
    {
        ArrayList<ImageDb> favorites = new ArrayList<ImageDb>();
        Statement s = null;
        try {
             s = DBInstance.getDBInstance().conn.createStatement();                
        } catch(SQLException e){e.printStackTrace();}
        
        // Resultset for all tuples
        ResultSet rs = null;
        String path, description, size, extension, topic, name;
        boolean confidentiality;
        ImageDb imd;
        
        try {
           rs = s.executeQuery("select x.path, name, description, size, topic, extension, confidentiality  from favorites, pictures x order by x.path where favorites.path = x.path");
           
           while (rs.next()){
               confidentiality = false;
               path = rs.getString("path");
               name = rs.getString("name");
               description = rs.getString("description");
               size = rs.getString("size");
               topic = rs.getString("topic");
               extension = rs.getString("extension");
               String conf = rs.getString("confidentiality");
               if (conf.equals("T")) confidentiality = true;
               
               imd = new ImageDb(path, topic, size, extension, description, name, confidentiality);
               favorites.add(imd);               
           }
           
        }catch (SQLException e){e.printStackTrace();}
                 
        // try to close the statement
           try {
                 s.close(); } catch(SQLException e) {e.printStackTrace();}
        
        return favorites;
    }
    
    
     /* Returns an arraylist with all images that satisfies the query. */
    public ArrayList<ImageDb> requestImagesFromQuery(ImageDb imd)
    {
        ArrayList<ImageDb> imagesfromquery = new ArrayList<ImageDb>();
        Statement s = null;
        
        try {
             s = DBInstance.getDBInstance().conn.createStatement();                
        } catch(SQLException e){e.printStackTrace();}
        
        // Resultset for all tuples
        ResultSet rs = null;
        String path, description, size, extension, topic, name;
        boolean confidentiality;        
        String SELECT = "select path, name, description, size, topic, extension, confidentiality  from pictures order by path";
        String WHERE = " where path != ' ' ";
        
        confidentiality = imd.getConfidentiality();      
        name = imd.getName();
        description = imd.getDescription();
        size = imd.getSize();
        topic = imd.getTopic();
        extension = imd.getExtension();
              
         if (name != null)
             WHERE += " and name like '%" + name + "%'";
         if (description != null)
             WHERE += " and description like '%" + description +"%'";
         if (topic != null)
             WHERE += " and topic like '%" + topic +"%'";
         if (extension != null)
             WHERE += " and topic like '%" + extension + "'%";
         if (size != null)
             WHERE += " and size like '%" + size + "'%";
         if (confidentiality)
             WHERE += " and confidentiality = 'T'";
         if (!confidentiality)
             WHERE += " and confidentiality = 'F'";                  
        
        try {
            
            rs = s.executeQuery( SELECT + WHERE);
           
           while (rs.next()){
               confidentiality = false;
               path = rs.getString("path");
               name = rs.getString("name");
               description = rs.getString("description");
               size = rs.getString("size");
               topic = rs.getString("topic");
               extension = rs.getString("extension");
               String conf = rs.getString("confidentiality");
               if (conf.equals("T")) confidentiality = true;
               
               imd = new ImageDb(path, topic, size, extension, description, name, confidentiality);
               imagesfromquery.add(imd);               
           }
           
        }catch (SQLException e){e.printStackTrace();}
                 
        // try to close the statement
           try {
                 s.close(); } catch(SQLException e) {e.printStackTrace();}
        
        return imagesfromquery;
    }
    
    /* Returns an ImageDb obejct from the specified path. */
    public ImageDb requestImageFromPath(String path){
        
        ImageDb imd = new ImageDb();
        Statement s = null;
        
        try {
             s = DBInstance.getDBInstance().conn.createStatement();                
        } catch(SQLException e){e.printStackTrace();}
        
        ResultSet rs = null;
        try{
        
             System.out.println("BD: " + path);
             rs = s.executeQuery("" +
"select path, name, description, size, topic, extension, confidentiality  from pictures order by path where pictures.path = '" + path + "'");
             
               if (rs.next())
                    {
                        imd.setConfidentiality(false);
                        imd.setDescription(rs.getString("description"));
                        imd.setPath(rs.getString("path"));
                        imd.setExtension(rs.getString("extension"));
                        imd.setName(rs.getString("name"));
                        imd.setSize(rs.getString("size"));
                        imd.setTopic(rs.getString("topic"));
                        if (rs.getString("confidentiality").equals("T")) imd.setConfidentiality(true);
                    } 
             
         }catch (SQLException e){e.printStackTrace();}
                
        
        // try to close the statement
           try {
                 s.close(); } catch(SQLException e) {e.printStackTrace();}
        
        return imd;
    }
    
    /* Add a new row into favorites table */
    public void addToFavorites(String path){
        
        Statement s = null;
        try {
             s = DBInstance.getDBInstance().conn.createStatement();                
        } catch(SQLException e){e.printStackTrace();}
        
        try{
            s.executeUpdate("insert into favorites values( '" + path + "')");
        }catch (SQLException t){t.printStackTrace();}
        
    }
    
    public ArrayList<String> queryPicturesTable(){
        ArrayList<String> im = new ArrayList<String>();
        
        Statement s = null;
        try {
             s = DBInstance.getDBInstance().conn.createStatement();                
        } catch(SQLException e){e.printStackTrace();}
        
        try{
            ResultSet rs = s.executeQuery("select path from pictures order by path");
            while (rs.next())
                im.add(rs.getString(1));
        }catch (SQLException t){t.printStackTrace();}
        return im;
    }
    
    public void printResultSet(ResultSet rs){
        
        try{
            while (rs.next())
                System.out.println(rs.getString(1));
        }catch (SQLException e){e.printStackTrace();}
        
    }
    
    public int getNumberOfTuples(String table){
        Statement s = null;
        int count = 0;
        
        try {
             s = DBInstance.getDBInstance().conn.createStatement();                
        } catch (SQLException e){e.printStackTrace();}
        
        try{
            ResultSet rs = s.executeQuery("select count(*) num from " + table);
            rs.next();
            count = rs.getInt("num");
            
            
        }catch (SQLException e){e.printStackTrace();}
        
        return count;
    }
    
    
    public void alterPictureTables(ImageDb imd){ 
        Statement s = null;
        String conf = "F";
        try{
            try {
                 s = DBInstance.getDBInstance().conn.createStatement();                
            } catch(SQLException e){System.out.println(" Error trying to update tuple "); e.printStackTrace();}
            if(imd.getConfidentiality())
                conf = "T";
            s.executeUpdate("update pictures set name = '" + imd.getName() +  "', topic = '" + imd.getTopic() + "', extension = '" + imd.getExtension() + "', size = '" + imd.getSize() + "', description = '" + imd.getDescription() + "', confidentiality = '" + conf + "' where path = '" + imd.getPath() + "'") ;           
            
        }catch (SQLException e){e.printStackTrace();}
    }
    
    // Subject interface methods.
    public void registerObserver(Observer O){ 
        observers.add(O);
    }
    public void removeObserver(Observer O){
        observers.remove(O);
    }    
    public void notifyObservers(){
        Iterator I = observers.iterator();
        while (I.hasNext())
            ((Observer)(I.next())).update();        
    }

    public ArrayList<ImageDb> getPictures() {
        ArrayList<ImageDb> pictures = new ArrayList<ImageDb>();
        Statement s = null;
        try {
             s = DBInstance.getDBInstance().conn.createStatement();                
        } catch(SQLException e){e.printStackTrace();}
        
        // Resultset for all tuples
        ResultSet rs = null;
        String path, description, size, extension, topic, name;
        boolean confidentiality;
        ImageDb imd;
        
        try {
           rs = s.executeQuery("select path, name, description, size, topic, extension, confidentiality  from pictures order by path");
           
           while (rs.next()){
               confidentiality = false;
               path = rs.getString("path");
               name = rs.getString("name");
               description = rs.getString("description");
               size = rs.getString("size");
               topic = rs.getString("topic");
               extension = rs.getString("extension");
               String conf = rs.getString("confidentiality");
               if (conf.equals("T")) confidentiality = true;
               
               imd = new ImageDb(path, topic, size, extension, description, name, confidentiality);
               pictures.add(imd);               
           }
           
        }catch (SQLException e){e.printStackTrace();}
                 
        // try to close the statement
           try {
                 s.close(); } catch(SQLException e) {e.printStackTrace();}
        
        return pictures;       
    }
    
    public ArrayList<ImageDb> getPictures(int begin,int offset) {
        ArrayList<ImageDb> pictures = new ArrayList<ImageDb>();
        Statement s = null;
        try {
             s = DBInstance.getDBInstance().conn.createStatement();                
        } catch(SQLException e){e.printStackTrace();}
        
        // Resultset for all tuples
        ResultSet rs = null;
        String path, description, size, extension, topic, name;
        boolean confidentiality;
        ImageDb imd;
        
        try {
           rs = s.executeQuery("select path, name, description, size, topic, extension, confidentiality  from pictures order by path");
           int counter = 0;
           
           while (counter != begin && rs.next()  ) counter++;
           
           counter = 0;
           
           while (rs.next() && counter != offset){
               confidentiality = false;
               path = rs.getString("path");
               //System.out.println("path " + path + " , counter " + counter);
               name = rs.getString("name");
               description = rs.getString("description");
               size = rs.getString("size");
               topic = rs.getString("topic");
               extension = rs.getString("extension");
               String conf = rs.getString("confidentiality");
               if (conf.equals("T")) confidentiality = true;
               
               imd = new ImageDb(path, topic, size, extension, description, name, confidentiality);
               pictures.add(imd); 
               counter++;
           }
           
        }catch (SQLException e){e.printStackTrace();}
                 
        // try to close the statement
           try {
                 s.close(); } catch(SQLException e) {e.printStackTrace();}
        
        return pictures;       
    }
    
    
}